//
//  CherryPickerDB.m
//  cherryPicker
//
//  Created by Young Soo Yun on 12. 1. 4..
//  Copyright (c) 2012 Happy. All rights reserved.
//

#import "CherryPickerDB.h"

#import "AppDelegate.h"
#import "DateUtil.h"

@implementation CherryPickerDB

+(BOOL)updateToCardList:(CardListDB*)cardData{
	AppDelegate* appDelegate = (AppDelegate*)[[UIApplication sharedApplication] delegate];
	sqlite3 *database;
    
    if(sqlite3_open([[appDelegate getDBPath] UTF8String], &database) == SQLITE_OK) {
		sqlite3_stmt *stmt;
		//insert
		const char *sql = "UPDATE CardList SET CardName = ?, Company = ?, CardType = ?, PayDate = ?, UseDate = ?, LimitPrice = ? WHERE Delimiter = ?";
		if(sqlite3_prepare_v2(database, sql, -1, &stmt, NULL) == SQLITE_OK) {	
            sqlite3_bind_text(stmt, 1, [cardData._CardName UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_text(stmt, 2, [cardData._Company UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_int(stmt, 3, cardData._CardType);
            sqlite3_bind_int(stmt, 4, cardData._PayDate);
            sqlite3_bind_int(stmt, 5, cardData._UseDate);
            sqlite3_bind_int(stmt, 6, cardData._LimitPrice);
            sqlite3_bind_text(stmt, 7, [cardData._Delimiter UTF8String], -1, SQLITE_TRANSIENT);
		}
		
		if(sqlite3_step(stmt) == SQLITE_DONE) {
	       	sqlite3_finalize(stmt);
		}
	}
	sqlite3_close(database); 
	return YES;
    
}

+(BOOL)insertFromCardList:(CardListDB*)cardData{
	AppDelegate* appDelegate = (AppDelegate*)[[UIApplication sharedApplication] delegate];
	sqlite3 *database;
    
    if(sqlite3_open([[appDelegate getDBPath] UTF8String], &database) == SQLITE_OK) {
		sqlite3_stmt *stmt;
		//insert
		const char *sql = "INSERT INTO CardList (CardName, Delimiter, Company, CardType, PayDate, UseDate, LimitPrice) VALUES(?, ?, ?, ?, ?, ?, ?)";
		if(sqlite3_prepare_v2(database, sql, -1, &stmt, NULL) == SQLITE_OK) {	
            sqlite3_bind_text(stmt, 1, [cardData._CardName UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_text(stmt, 2, [cardData._Delimiter UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_text(stmt, 3, [cardData._Company UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_int(stmt, 4, cardData._CardType);
            sqlite3_bind_int(stmt, 5, cardData._PayDate);
            sqlite3_bind_int(stmt, 6, cardData._UseDate);
            sqlite3_bind_int(stmt, 7, cardData._LimitPrice);
		}
		
		if(sqlite3_step(stmt) == SQLITE_DONE) {
	       	sqlite3_finalize(stmt);
		}
	}
	sqlite3_close(database); 
	return YES;

}

+(BOOL)deleteCardList:(NSString*)delimiter{
	AppDelegate* appDelegate = (AppDelegate*)[[UIApplication sharedApplication] delegate];
	sqlite3 *database;
    
    if(sqlite3_open([[appDelegate getDBPath] UTF8String], &database) == SQLITE_OK) {
		sqlite3_stmt *stmt;
		//insert
		const char *sql = "DELETE FROM CardList WHERE Delimiter = ?";
		if(sqlite3_prepare_v2(database, sql, -1, &stmt, NULL) == SQLITE_OK) {	
            sqlite3_bind_text(stmt, 1, [delimiter UTF8String], -1, SQLITE_TRANSIENT);
		}
		
		if(sqlite3_step(stmt) == SQLITE_DONE) {
	       	sqlite3_finalize(stmt);
		}
	}
	sqlite3_close(database); 
	return YES;
}

+(BOOL)updateToReceipt:(ReceiptDB*)receiptData{
	AppDelegate* appDelegate = (AppDelegate*)[[UIApplication sharedApplication] delegate];
	sqlite3 *database;
	if(sqlite3_open([[appDelegate getDBPath] UTF8String], &database) == SQLITE_OK) {
		sqlite3_stmt *stmt;
		//insert
		const char *sql = "UPDATE Receipt SET CardName = ?, Date = ?, Price = ?, Category = ?, Discount = ?, Delimiter = ?, Place = ? WHERE Id = ?";
		if(sqlite3_prepare_v2(database, sql, -1, &stmt, NULL) == SQLITE_OK) {	
            //			sqlite3_exec(database, "BEGIN;", NULL, NULL, NULL);
            sqlite3_bind_text(stmt, 1, [receiptData._CardName UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_text(stmt, 2, [receiptData._Date UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_int(stmt, 3, receiptData._Price);
            sqlite3_bind_text(stmt, 4, [receiptData._Category UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_int(stmt, 5, receiptData._Discount);
            sqlite3_bind_text(stmt, 6, [receiptData._Delimiter UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_text(stmt, 7, [receiptData._Place UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_int(stmt, 8, receiptData._Id);
            
            //            sqlite3_exec(database, "COMMIT;", NULL, NULL, NULL);
		}
		
		if(sqlite3_step(stmt) == SQLITE_DONE) {
	       	sqlite3_finalize(stmt);
		}
	}
	sqlite3_close(database); 
	return YES;
}

+(BOOL)updateToReceipt:(NSString*)delimiter
           newCardName:(NSString*)newCardName{
    AppDelegate* appDelegate = (AppDelegate*)[[UIApplication sharedApplication] delegate];
	sqlite3 *database;
	if(sqlite3_open([[appDelegate getDBPath] UTF8String], &database) == SQLITE_OK) {
		sqlite3_stmt *stmt;
		//insert
		const char *sql = "UPDATE Receipt SET CardName = ? WHERE Delimiter = ?";
		if(sqlite3_prepare_v2(database, sql, -1, &stmt, NULL) == SQLITE_OK) {	
            //			sqlite3_exec(database, "BEGIN;", NULL, NULL, NULL);
            sqlite3_bind_text(stmt, 1, [newCardName UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_text(stmt, 2, [delimiter UTF8String], -1, SQLITE_TRANSIENT);
            
		}
		
		if(sqlite3_step(stmt) == SQLITE_DONE) {
	       	sqlite3_finalize(stmt);
		}
	}
	sqlite3_close(database); 
	return YES;
}

+(BOOL)deleteReceiptWhereCard:(NSString*)delimiter{
	AppDelegate* appDelegate = (AppDelegate*)[[UIApplication sharedApplication] delegate];
	sqlite3 *database;
    
    if(sqlite3_open([[appDelegate getDBPath] UTF8String], &database) == SQLITE_OK) {
		sqlite3_stmt *stmt;
		//insert
		const char *sql = "DELETE FROM Receipt WHERE Delimiter = ?";
		if(sqlite3_prepare_v2(database, sql, -1, &stmt, NULL) == SQLITE_OK) {	
            sqlite3_bind_text(stmt, 1, [delimiter UTF8String], -1, SQLITE_TRANSIENT);
		}
		
		if(sqlite3_step(stmt) == SQLITE_DONE) {
	       	sqlite3_finalize(stmt);
		}
	}
	sqlite3_close(database); 
	return YES;
}

+(BOOL)deleteReceiptWhereID:(NSInteger)ID{
	AppDelegate* appDelegate = (AppDelegate*)[[UIApplication sharedApplication] delegate];
	sqlite3 *database;
    
    if(sqlite3_open([[appDelegate getDBPath] UTF8String], &database) == SQLITE_OK) {
		sqlite3_stmt *stmt;
		//insert
		const char *sql = "DELETE FROM Receipt WHERE Id = ?";
		if(sqlite3_prepare_v2(database, sql, -1, &stmt, NULL) == SQLITE_OK) {	
            sqlite3_bind_int(stmt, 1, ID);
		}
		
		if(sqlite3_step(stmt) == SQLITE_DONE) {
	       	sqlite3_finalize(stmt);
		}
	}
	sqlite3_close(database); 
	return YES;
}

+(BOOL)insertFromReceipt:(ReceiptDB*)receiptData{
	AppDelegate* appDelegate = (AppDelegate*)[[UIApplication sharedApplication] delegate];
	sqlite3 *database;
	if(sqlite3_open([[appDelegate getDBPath] UTF8String], &database) == SQLITE_OK) {
		sqlite3_stmt *stmt;
		//insert
		const char *sql = "INSERT INTO Receipt(CardName, Date, Price, Category, Discount, Delimiter, Place) values(?, ?, ?, ?, ?, ?, ?)";
		if(sqlite3_prepare_v2(database, sql, -1, &stmt, NULL) == SQLITE_OK) {	
//			sqlite3_exec(database, "BEGIN;", NULL, NULL, NULL);
            sqlite3_bind_text(stmt, 1, [receiptData._CardName UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_text(stmt, 2, [receiptData._Date UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_int(stmt, 3, receiptData._Price);
            sqlite3_bind_text(stmt, 4, [receiptData._Category UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_int(stmt, 5, receiptData._Discount);
            sqlite3_bind_text(stmt, 6, [receiptData._Delimiter UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_text(stmt, 7, [receiptData._Place UTF8String], -1, SQLITE_TRANSIENT);
            
//            sqlite3_exec(database, "COMMIT;", NULL, NULL, NULL);
		}
		
		if(sqlite3_step(stmt) == SQLITE_DONE) {
	       	sqlite3_finalize(stmt);
		}
	}
	sqlite3_close(database); 
	return YES;
}

+(NSArray*)selectFromCardList{
	AppDelegate* appDelegate = (AppDelegate*)[[UIApplication sharedApplication] delegate];
	NSArray* cardLists = nil;
	sqlite3* database;
	if(sqlite3_open([[appDelegate getDBPath] UTF8String], &database) == SQLITE_OK) {
		// sql
		const char *sql = "SELECT CardName, Delimiter, Company, CardType, PayDate, UseDate, LimitPrice FROM CardList";
		sqlite3_stmt *stmt;
		if(sqlite3_prepare_v2(database, sql, -1, &stmt, NULL) == SQLITE_OK) {
			// Loop through the results and add them to the feeds array
			cardLists = [[self class] readCardListRows:stmt];
		}
		// Release the compiled statement from memory
		sqlite3_finalize(stmt);
	}
	sqlite3_close(database);
	return cardLists;
}

+(NSArray*)selectFromCardList:(NSString*)delimiter{
	AppDelegate* appDelegate = (AppDelegate*)[[UIApplication sharedApplication] delegate];
	NSArray* cardLists = nil;
	sqlite3* database;
	if(sqlite3_open([[appDelegate getDBPath] UTF8String], &database) == SQLITE_OK) {
		// sql
		const char *sql = "SELECT CardName, Delimiter, Company, CardType, PayDate, UseDate, LimitPrice FROM CardList where Delimiter = ?";
		sqlite3_stmt *stmt;
		if(sqlite3_prepare_v2(database, sql, -1, &stmt, NULL) == SQLITE_OK) {
            sqlite3_bind_text(stmt, 1, [delimiter UTF8String], -1, SQLITE_STATIC);
			// Loop through the results and add them to the feeds array
			cardLists = [[self class] readCardListRows:stmt];
		}
		// Release the compiled statement from memory
		sqlite3_finalize(stmt);
	}
	sqlite3_close(database);
	return cardLists;
}

+(NSArray *)readCardListRows:(sqlite3_stmt *)stmt{
	NSMutableArray *lists = [[NSMutableArray alloc] init];
	
	while(sqlite3_step(stmt) == SQLITE_ROW) {
		// Read the data from the result row
		NSString* cardName = [NSString stringWithUTF8String:(char *)sqlite3_column_text(stmt, 0)];
		NSString* delimiter = [NSString stringWithUTF8String:(char *)sqlite3_column_text(stmt, 1)];
		NSString* company = [NSString stringWithUTF8String:(char *)sqlite3_column_text(stmt, 2)];
        NSInteger cardType = sqlite3_column_int(stmt, 3);
        NSInteger payDate = sqlite3_column_int(stmt, 4);
        NSInteger useDate = sqlite3_column_int(stmt, 5);
        NSInteger limitPrice = sqlite3_column_int(stmt, 6);
        
		CardListDB* cardList = [[CardListDB alloc] initWithCode:cardName
                                                      delimiter:delimiter
                                                        company:company
                                                       cardType:cardType
                                                        payDate:payDate
                                                        useDate:useDate
                                                     limitPrice:limitPrice];
		
		[lists addObject:cardList];
		[cardList release];
	}
	return lists;
}

+(NSArray*)selectFromReceipt{
	AppDelegate* appDelegate = (AppDelegate*)[[UIApplication sharedApplication] delegate];
	NSArray* receiptList = nil;
	sqlite3* database;
	if(sqlite3_open([[appDelegate getDBPath] UTF8String], &database) == SQLITE_OK) {
		// sql
		const char *sql = "SELECT CardName, Date, Price, Category, Discount, Delimiter, Place FROM Receipt";
		sqlite3_stmt *stmt;
		if(sqlite3_prepare_v2(database, sql, -1, &stmt, NULL) == SQLITE_OK) {
			// Loop through the results and add them to the feeds array
			receiptList = [[self class] readReceiptRows:stmt];
		}
		// Release the compiled statement from memory
		sqlite3_finalize(stmt);
	}
	sqlite3_close(database);
	return receiptList;
}

+(NSArray*)selectFromReceipt:(NSString*)delimiter{
	AppDelegate* appDelegate = (AppDelegate*)[[UIApplication sharedApplication] delegate];
	NSArray* receiptList = nil;
	sqlite3* database;
	if(sqlite3_open([[appDelegate getDBPath] UTF8String], &database) == SQLITE_OK) {
		// sql
		const char *sql = "SELECT Id, CardName, Date, Price, Category, Discount, Delimiter, Place FROM Receipt where Delimiter = ?";
		sqlite3_stmt *stmt;
		if(sqlite3_prepare_v2(database, sql, -1, &stmt, NULL) == SQLITE_OK) {
            sqlite3_bind_text(stmt, 1, [delimiter UTF8String], -1, SQLITE_STATIC);
			// Loop through the results and add them to the feeds array
			receiptList = [[self class] readReceiptRows:stmt];
		}
		// Release the compiled statement from memory
		sqlite3_finalize(stmt);
	}
	sqlite3_close(database);
	return receiptList;
}

+(NSArray *)readReceiptRows:(sqlite3_stmt *)stmt{
	NSMutableArray *lists = [[NSMutableArray alloc] init];
	
	while(sqlite3_step(stmt) == SQLITE_ROW) {
		// Read the data from the result row
        NSInteger Id = sqlite3_column_int(stmt,0);
		NSString* cardName = [NSString stringWithUTF8String:(char *)sqlite3_column_text(stmt, 1)];
		NSString* checkDate = [NSString stringWithUTF8String:(char *)sqlite3_column_text(stmt, 2)];
        NSInteger price = sqlite3_column_int(stmt,3);
		NSString* category = [NSString stringWithUTF8String:(char *)sqlite3_column_text(stmt, 4)];
        NSInteger discount = sqlite3_column_int(stmt, 5);
        NSString* delimiter = [NSString stringWithUTF8String:(char *)sqlite3_column_text(stmt, 6)];
        NSString* place = [NSString stringWithUTF8String:(char *)sqlite3_column_text(stmt, 7)];
        
		ReceiptDB* receipt = [[ReceiptDB alloc] initWithCode:Id
                                                    cardName:cardName
                                                        date:checkDate
                                                       price:price
                                                    category:category
                                                    discount:discount
                                                   delimiter:delimiter
                                                       place:place];
		
		[lists addObject:receipt];
		[receipt release];
	}
	return lists;
}

+(NSString*)selectFromReceiptForSum:(NSString *)delimiter{
    NSArray* cardList = [self selectFromCardList:delimiter];
    
    if (cardList == nil || cardList.count == 0) {
        return @"";
    }
    
    CardListDB* selectedCard = [cardList objectAtIndex:0];
    
    NSString* nowDate = [DateUtil nowDateStr];
    NSString* startDateStr;
    NSString* endDateStr;
    
    NSString* thisMonth = [[DateUtil formatYYMMDDFromDateStr:[DateUtil nowDateStr]] substringToIndex:8];
    NSString* preMonth = [[DateUtil formatYYMMDDFromDateStr:[DateUtil prevDateStr:1]] substringToIndex:8];
    NSString* before2Month = [[DateUtil formatYYMMDDFromDateStr:[DateUtil prevDateStr:2]] substringToIndex:8];
    
    NSString* startDay;
    NSString* endDay;
    
    if (selectedCard._CardType == 0) {
        if (selectedCard._UseDate < 9) {
            startDay = [NSString stringWithFormat:@"0%d", selectedCard._UseDate + 1];
        }else{
            startDay = [NSString stringWithFormat:@"%d", selectedCard._UseDate + 1];
        }
        
        if (selectedCard._UseDate < 10) {
            endDay = [NSString stringWithFormat:@"0%d", selectedCard._UseDate];
        }else{
            endDay = [NSString stringWithFormat:@"%d", selectedCard._UseDate];
        }
        
        if ([[nowDate substringWithRange:NSMakeRange(6, 2)] intValue] < selectedCard._PayDate) {
            startDateStr = [NSString stringWithFormat:@"%@%@", before2Month, startDay];
            endDateStr = [NSString stringWithFormat:@"%@%@", preMonth, endDay];
        }else{
            startDateStr = [NSString stringWithFormat:@"%@%@", preMonth, startDay];
            endDateStr = [NSString stringWithFormat:@"%@%@", thisMonth, endDay];
        }
        
        NSString* sumResult = [self selectFromReceiptForSum:delimiter dateStart:startDateStr dateEnd:endDateStr];
        
        return sumResult;
    }
    
    startDateStr = [NSString stringWithFormat:@"%@01", thisMonth];
    endDateStr = [DateUtil lastDayOfMonth:startDateStr];
    
    NSString* sumResult = [self selectFromReceiptForSum:delimiter dateStart:startDateStr dateEnd:endDateStr];
    
    return sumResult;
}

+(NSString*)selectFromReceiptForSum:(NSString*)delimiter
                          dateStart:(NSString*)dateStart
                            dateEnd:(NSString*)dateEnd{
	AppDelegate* appDelegate = (AppDelegate*)[[UIApplication sharedApplication] delegate];
    NSString* sumResult = nil;
	sqlite3* database;
	if(sqlite3_open([[appDelegate getDBPath] UTF8String], &database) == SQLITE_OK) {
		// sql
		const char *sql = "SELECT sum(Price-Discount) FROM Receipt where Delimiter = ? and Date >= ? and Date <= ?";
		sqlite3_stmt *stmt;
		if(sqlite3_prepare_v2(database, sql, -1, &stmt, NULL) == SQLITE_OK) {
			
            sqlite3_bind_text(stmt, 1, [delimiter UTF8String], -1, SQLITE_STATIC);
            sqlite3_bind_text(stmt, 2, [dateStart UTF8String], -1, SQLITE_STATIC);
            sqlite3_bind_text(stmt, 3, [dateEnd UTF8String], -1, SQLITE_STATIC);
            // Loop through the results and add them to the feeds array
            while(sqlite3_step(stmt) == SQLITE_ROW) {
                // Read the data from the result row
                sumResult = [NSString stringWithFormat:@"%d", sqlite3_column_int(stmt, 0)];
            }
		}
		// Release the compiled statement from memory
		sqlite3_finalize(stmt);
	}
	sqlite3_close(database);
	return sumResult;
}



@end


@implementation CardListDB

@synthesize _CardName;
@synthesize _Delimiter;
@synthesize _Company;
@synthesize _CardType;
@synthesize _PayDate;
@synthesize _UseDate;
@synthesize _LimitPrice;

-(id)initWithCode:(NSString *)cardName
        delimiter:(NSString *)delimiter
          company:(NSString *)company
         cardType:(NSInteger)cardType
          payDate:(NSInteger)payDate
          useDate:(NSInteger)useDate
       limitPrice:(NSInteger)limitPrice{
	if( self = [super init] ) {
		_CardName = [[NSString alloc] initWithString:cardName];
		_Delimiter = [[NSString alloc] initWithString:delimiter];
		_Company = [[NSString alloc] initWithString:company];
        _CardType = cardType;
		_PayDate = payDate;
		_UseDate = useDate;
        _LimitPrice = limitPrice;
	}
	return self;
}

@end

@implementation ReceiptDB

@synthesize _Id;
@synthesize _CardName;
@synthesize _Date;
@synthesize _Price;
@synthesize _Category;
@synthesize _Discount;
@synthesize _Delimiter;
@synthesize _Place;

-(id)initWithCode:(NSInteger)Id
         cardName:(NSString *)cardName
             date:(NSString *)date
            price:(NSInteger)price
         category:(NSString *)category
         discount:(NSInteger)discount
        delimiter:(NSString*)delimiter
            place:(NSString*)place{
	if( self = [super init] ) {
        _Id = Id;
		_CardName = [[NSString alloc] initWithString:cardName];
		_Date = [[NSString alloc] initWithString:date];
		_Category = [[NSString alloc] initWithString:category];
        _Price = price;
		_Discount = discount;
        _Delimiter = [[NSString alloc] initWithString:delimiter];
        _Place = [[NSString alloc] initWithString:place];
	}
	return self;
}

@end